﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

using iTool.Cloud.Database.iToolService;
using iTool.Cloud.Database.Options;
using iTool.Cloud.Database.ServiceProvider;
using iTool.Cloud.Database.SqlStructureProvider.Options;
using iTool.Cloud.DataSearch.ServiceProvider;
using iTool.SQL.AIHandle;

using Microsoft.Data.Sqlite;

using Orleans;

using static Lucene.Net.Util.Fst.Util;

namespace iTool.Cloud.Database.SyncDatabaseProvider
{
    public class SyncDatabaseService : ISyncDatabaseService
    {
        const string DirectoryPath = "./Storage";
        const string DataSource = DirectoryPath + "/DataBase.master";

        readonly string TableName;
        readonly string SqliteDBVersionConnectionInstanceOfPrivate;
        IGrainFactory iGrainFactory;
        bool isSyncing = false;

#pragma warning disable CS8618 // 在退出构造函数时，不可为 null 的字段必须包含非 null 值。请考虑声明为可以为 null。
        public SyncDatabaseService(string tableName, string sqliteDBVersionConnectionInstanceOfPrivate)
#pragma warning restore CS8618 // 在退出构造函数时，不可为 null 的字段必须包含非 null 值。请考虑声明为可以为 null。
        {
            TableName = tableName;
            iToolServiceDataBase.GetSqlStructureProviderByTableName(this.TableName);

            SqliteDBVersionConnectionInstanceOfPrivate = sqliteDBVersionConnectionInstanceOfPrivate;
        }

        public  Task<long> GetCurrentVersionAsync()
        {
            using (var connection = new SqliteConnection(this.SqliteDBVersionConnectionInstanceOfPrivate))
            {
                connection.Open();

                using (var command = new SqliteCommand(String.Intern($"select rowid from {this.TableName} order by rowid desc limit 1"), connection))
                {
                    var reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            return Task.FromResult(reader.GetInt64(0));
                        }
                    }
                }
            }

            return Task.FromResult<long>(0);
        }

        public async void SyncAsync(IGrainFactory grainFactory) 
        {
            Console.WriteLine("SyncAsync:{0}", isSyncing);

            if (isSyncing)
            {
                // 使用反应式，延迟50ms在执行 以防止密集写入导致循环操作
                return;
            }

            this.isSyncing = true;

            try
            {
                this.iGrainFactory = grainFactory;
                iTableSyncLoggerService syncLoggerService = this.iGrainFactory.GetGrain<iTableSyncLoggerService>(this.TableName);

                {
                    // 表字段
                    IEnumerable<FieldOptions> tableFields = await syncLoggerService.GetTableAllFieldAsync();
                    // TUDO 检查数据结构
                    await iToolServiceDataBase.GetSqlStructureProviderByTableName(this.TableName).CheckStructureAsync(tableFields.Select(item => item.Name));

                    // 索引字段
                    (List<string> locations, List<string> fields) indexFields = await syncLoggerService.GetAllIndexFieldAsync();
                    IIndexManageService indexManageService = IDirectoryServiceFactory<IndexManageService>.GetService(0, this.TableName);
                    await indexManageService.IsNeedReBuildIndexByFieldsAsync(indexFields.locations, indexFields.fields.ToArray());
                    // TUDO 检查索引字段（如果只读Service 修改了索引，那么需要同步调用 Exec Service 来同步索引。 Exec on Service 为该表权威表，如冲突最终以该服务数据库为准）
                    //      表结构也一样
                }

                // 读取同步的Logger
                await this.DoExcuSyncAsync(syncLoggerService);
            }
            catch (Exception ex)
            {
                Console.WriteLine("ex:{0}", ex.Message);
            }
            finally
            {
                this.isSyncing = false;
            }
        }

        private async Task DoExcuSyncAsync(iTableSyncLoggerService syncLoggerService)
        {
            var currentVersion = await this.GetCurrentVersionAsync();
            var loggers = await syncLoggerService.GetSyncSqlScriptAsync(currentVersion);

            Console.WriteLine("currentVersion:{0},loggers:{1}", currentVersion, loggers?.Count);

            if (loggers == null)
            {
                return;
            }

            var connection = new SqliteConnection(new SqliteConnectionStringBuilder
            {
                DataSource = DataSource,
                Mode = this is iTableReaderService ? SqliteOpenMode.ReadOnly : SqliteOpenMode.ReadWriteCreate,
                Cache = this is iTableReaderService ? SqliteCacheMode.Shared : SqliteCacheMode.Private,
                Pooling = true
            }.ToString());

            connection.Open();
            var trans = connection.BeginTransaction();
            
            try
            {
                foreach (var item in loggers)
                {
                    string sql = "";
                    var command = new SqliteCommand();
                    command.Connection = connection;
                    switch (item.Action)
                    {
                        case "INSERT":
                            sql = item.Sql;
                            command.Parameters.Add(new SqliteParameter("@B_IDX", item.Keys));
                            break;

                        case "UPDATE":
                            sql = item.Sql;
                            break;

                        case "DELETE":
                            if (item.Keys == "-1")
                            {
                                sql = $"DELETE FROM {this.TableName}";
                            }
                            else
                            {
                                sql = $"DELETE FROM {this.TableName} where _IDX in ({item.Keys})";
                            }
                            break;
                    }
                    command.Transaction = trans;
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                }

                trans.Commit();
            }
            catch (Exception)
            {
                trans.Rollback();
                throw;
            }

            connection.Close();

            // TODU 
            await this.AddExecLoggerAsync(loggers);

            await this.DoExcuSyncAsync(syncLoggerService);
        }

        private Task AddExecLoggerAsync(IEnumerable<BLoggerOptions> loggers)
        {
            iLock<iToolServiceDataBase>.EnterReadLock(this.TableName);
            try
            {
                using (var connection = new SqliteConnection(this.SqliteDBVersionConnectionInstanceOfPrivate))
                {
                    connection.Open();
                    using (var transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
                    {
                        try
                        {
                            using (var command = new SqliteCommand(String.Intern($"insert into {this.TableName}(action,sql,keys,extend,tranid,isCreateIndex) values($action,$sql,$keys,$extend,$tranid,0)"), connection))
                            {
                                command.Transaction = (SqliteTransaction)transaction;
                                // 是跟 connection 的， 如果已经执行过 Prepare，后续调用会直接返回 不会有其他影响
                                //command.Prepare();
                                var actionParams = new SqliteParameter { ParameterName = "$action" };
                                var sqlParams = new SqliteParameter { ParameterName = "$sql" };
                                var keysParams = new SqliteParameter { ParameterName = "$keys" };
                                var extendParams = new SqliteParameter { ParameterName = "$extend" };
                                var tranidParams = new SqliteParameter { ParameterName = "$tranid" };
                                command.Parameters.Add(actionParams);
                                command.Parameters.Add(sqlParams);
                                command.Parameters.Add(keysParams);
                                command.Parameters.Add(extendParams);
                                command.Parameters.Add(tranidParams);
                                foreach (var item in loggers)
                                {
                                    actionParams.Value = item.Action;
                                    sqlParams.Value = item.Sql;
                                    keysParams.Value = item.Keys;
                                    extendParams.Value = item.Extend;
                                    tranidParams.Value = item.TranId;
                                    command.ExecuteNonQuery();
                                }
                            }
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            throw ex;
                        }
                    }
                }
            }
            finally
            {
                iLock<iToolServiceDataBase>.ExitReadLock(this.TableName);
            }

            return Task.CompletedTask;
        }
    }
}
